using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.OleDb;
using System.Data;

namespace DAOLayer
{
    public class PhieuThueDao
    {
        public static void Them(PhieuThueDto p)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Insert into PHIEUTHUE ( MaPhong,NgayBatDauThue,DonGiaThue,HeSoThue,TyLePhuThuKhiThue,SoLuongKhach,DaThanhToan ) values(?,?, ?,?,?,?,?)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaPhong", OleDbType.Integer);
            cmd.Parameters["@MaPhong"].Value = p.MaPhong;
            cmd.Parameters.Add("@NgayBatDauThue", OleDbType.DBDate);
            cmd.Parameters["@NgayBatDauThue"].Value = p.NgayBatDauThue;
            cmd.Parameters.Add("@DonGiaThue", OleDbType.Integer);
            cmd.Parameters["@DonGiaThue"].Value = p.DonGiaThue;
            cmd.Parameters.Add("@HeSoThue", OleDbType.Double);
            cmd.Parameters["@HeSoThue"].Value = p.HeSoThue;
            cmd.Parameters.Add("@TyLePhuThuKhiThue", OleDbType.Double);
            cmd.Parameters["@TyLePhuThuKhiThue"].Value = p.TyLePhuThuKhiThue;
            cmd.Parameters.Add("@SoLuongKhach", OleDbType.Integer);
            cmd.Parameters["@SoLuongKhach"].Value = p.SoLuongKhachThue;
            cmd.Parameters.Add("@DaThanhToan", OleDbType.Boolean);
            cmd.Parameters["@DaThanhToan"].Value = p.DaThanhToan;
            cmd.ExecuteNonQuery();

            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            p.MaPhieuThue = Convert.ToInt32(cmd.ExecuteScalar());
            // B5: Dong ket noi CSDL
            cn.Close();
        }
        public static PhieuThueDto LayMotPhieuThue(int maphong,bool flag)
        {
            PhieuThueDto pt = new PhieuThueDto();
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            //'B3: Tao chuoi strSQL thao tac CSDL
            String strSQL = "Select * From PHIEUTHUE Where DaThanhToan = ? and MaPhong = ? ";
            //'B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@DaThanhToan", OleDbType.Boolean);
            cmd.Parameters[("@DaThanhToan")].Value = flag;
            cmd.Parameters.Add("@MaPhong", OleDbType.Integer);
            cmd.Parameters[("@MaPhong")].Value = maphong;
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                pt.MaPhieuThue = (int)dr["MaPhieuThue"];
                pt.MaPhong = (int)dr["MaPhong"];
                pt.NgayBatDauThue = (DateTime)dr["NgayBatDauThue"];
                pt.SoLuongKhachThue= (int)dr["SoLuongKhach"];
                pt.TyLePhuThuKhiThue = (double)dr["TyLePhuThuKhiThue"];
                pt.HeSoThue = Convert.ToDouble(dr["HeSoThue"]);
                pt.DonGiaThue = (int)dr["DonGiaThue"];
            }
            cn.Close();
            return pt;

        }
        public static PhieuThueDto LayMotPhieuThueTheoMaPhieuThue(int mapt)
        {
            PhieuThueDto pt = new PhieuThueDto();
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            //'B3: Tao chuoi strSQL thao tac CSDL
            String strSQL = "Select * From PHIEUTHUE Where MaPhieuThue = ? ";
            //'B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaPhieuThue", OleDbType.Integer);
            cmd.Parameters[("@MaPhieuThue")].Value = mapt;
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                pt.MaPhieuThue = (int)dr["MaPhieuThue"];
                pt.MaPhong = (int)dr["MaPhong"];
                pt.NgayBatDauThue = (DateTime)dr["NgayBatDauThue"];
                pt.SoLuongKhachThue = (int)dr["SoLuongKhach"];
                pt.TyLePhuThuKhiThue = (double)dr["TyLePhuThuKhiThue"];
                pt.HeSoThue = (double)dr["HeSoThue"];
            }
            cn.Close();
            return pt;

        }
        public static void CapNhatXuongCSDL(PhieuThueDto p)
        {
            OleDbConnection cn = Database.ConnectionData();
            //'B3: Tao chuoi strSQL thao tac CSDL
            String strSQL = "Update PHIEUTHUE Set DaThanhToan = ?  Where MaPhieuThue = ?  ";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@DaThanhToan", OleDbType.Boolean);
            cmd.Parameters["@DaThanhToan"].Value = true;
            cmd.Parameters.Add("@MaPhieuThue", OleDbType.Integer);
            cmd.Parameters["@MaPhieuThue"].Value = p.MaPhieuThue;           
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public static DataTable LayDanhSachPhieuThue()
        {
            DataTable dt = new DataTable();
            OleDbConnection cn = Database.ConnectionData();
            //'B3: Tao chuoi strSQL thao tac CSDL
            String strSQL = " Select  *";
            strSQL += " From PHIEUTHUE ";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        
    }
}
